import json
from typing import Union, Optional

from sqlalchemy import update, delete
from config import create_db_and_tables, session
from sqlmodel import SQLModel, Field, select, or_


class Team(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    headquarters: str


class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    secret_name: str
    age: Optional[int] = Field(default=None, index=True)

    team_id: Optional[int] = Field(default=None, foreign_key="team.id")


# create_db_and_tables()

# SELECT hero.id, hero.name, hero.secret_name, hero.age, hero.team_id, team.id AS id_1, team.name AS name_1, team.headquarters
#   FROM hero, team WHERE hero.team_id = team.id
def select_heroes_1():
    statement = select(Hero, Team).where(Hero.team_id == Team.id)
    results = session.exec(statement)
    for hero, team in results:
        print("Hero:", hero, "Team:", team)


# select_heroes_1()


# SELECT hero.id, hero.name, hero.secret_name, hero.age, hero.team_id, team.id AS id_1, team.name AS name_1, team.headquarters
#   FROM hero JOIN team ON team.id = hero.team_id
def select_heroes_2():
    statement = select(Hero, Team).join(Team)
    results = session.exec(statement)
    for hero, team in results:
        print("Hero:", hero, "Team:", team)


# select_heroes_2()


def select_heroes_3():
    statement = (select(Hero, Team)
                 .join_from(Hero, Team, Hero.team_id == Team.id)
                 .where(Team.id == 2))
    results = session.exec(statement)
    for hero, team in results:
        print("Hero:", hero, "Team:", team)


# select_heroes_3()

def select_heroes_3():
    statement = (select(Hero, Team)
                 .join_from(Hero, Team, Hero.team_id == Team.id, isouter=True)
                 .where(Team.id == 2))
    results = session.exec(statement)
    for hero, team in results:
        print("Hero:", hero, "Team:", team)


# select_heroes_3()


class SysUser(SQLModel, table=True):
    __tablename__ = 'sys_user'
    user_id: Optional[int] = Field(default=None, primary_key=True)
    user_name: str = Field(index=True)


class SysRole(SQLModel, table=True):
    __tablename__ = 'sys_role'
    role_id: Optional[int] = Field(default=None, primary_key=True)
    role_name: str


class SysUserRole(SQLModel, table=True):
    __tablename__ = 'sys_user_role'
    id: Optional[int] = Field(default=None, primary_key=True)
    user_id: Optional[int] = Field(default=None)
    role_id: Optional[int] = Field(default=None)

# create_db_and_tables()
